import pandas as pd


# 规则函数
def check_and_fix(df):
    # B列: gearbox_temp_input_shaft1 < 80
    df['B'] = df['B'].apply(lambda x: min(x, 80) if pd.notna(x) else x)

    # C列: gearbox_temp_output_shaft2 < 80
    df['C'] = df['C'].apply(lambda x: min(x, 80) if pd.notna(x) else x)

    # D列: gearbox_oil_temperature_gearbox < 70
    df['D'] = df['D'].apply(lambda x: min(x, 70) if pd.notna(x) else x)

    # E列: generator_winding_temperature_u1 < 135
    df['E'] = df['E'].apply(lambda x: min(x, 135) if pd.notna(x) else x)

    # F列: generator_winding_temperature_v1 < 135
    df['F'] = df['F'].apply(lambda x: min(x, 135) if pd.notna(x) else x)

    # G列: generator_winding_temperature_w1 < 135
    df['G'] = df['G'].apply(lambda x: min(x, 135) if pd.notna(x) else x)

    # H列: generator_bearing_temperature_a < 80
    df['H'] = df['H'].apply(lambda x: min(x, 80) if pd.notna(x) else x)

    # I列: generator_bearing_temperature_b < 80
    df['I'] = df['I'].apply(lambda x: min(x, 80) if pd.notna(x) else x)

    # J列: nacelle_temperature < 50
    df['J'] = df['J'].apply(lambda x: min(x, 50) if pd.notna(x) else x)

    # L列: tower_base_cabinet_temperature < 50
    df['L'] = df['L'].apply(lambda x: min(x, 50) if pd.notna(x) else x)

    # M列: topbox_cabinet_temperature < 50
    df['M'] = df['M'].apply(lambda x: min(x, 50) if pd.notna(x) else x)

    # N列: main_bearing_rotor_side_temperature < 60
    df['N'] = df['N'].apply(lambda x: min(x, 60) if pd.notna(x) else x)

    # O列: main_bearing_gearbox_side_temperature < 60
    df['O'] = df['O'].apply(lambda x: min(x, 60) if pd.notna(x) else x)

    # P列: 齿轮箱前轴与油温差值不超过20
    df['P'] = df.apply(
        lambda row: min(row['P'], 20) if pd.notna(row['P']) and abs(row['B'] - row['D']) > 20 else row['P'], axis=1)

    # Q列: 齿轮箱后轴与油温差值不超过18
    df['Q'] = df.apply(
        lambda row: min(row['Q'], 18) if pd.notna(row['Q']) and abs(row['C'] - row['D']) > 18 else row['Q'], axis=1)

    # R列: 齿轮箱前后轴差值不超过15
    df['R'] = df.apply(
        lambda row: min(row['R'], 15) if pd.notna(row['R']) and abs(row['B'] - row['C']) > 15 else row['R'], axis=1)

    # S列: 主轴前后差值不超过20
    df['S'] = df.apply(
        lambda row: min(row['S'], 20) if pd.notna(row['S']) and abs(row['N'] - row['O']) > 20 else row['S'], axis=1)

    # T列: 发电机前后差值不超过20
    df['T'] = df.apply(
        lambda row: min(row['T'], 20) if pd.notna(row['T']) and abs(row['E'] - row['F']) > 20 else row['T'], axis=1)

    # U列: 发电机绕组差值1不超过15
    df['U'] = df.apply(
        lambda row: min(row['U'], 15) if pd.notna(row['U']) and abs(row['E'] - row['F']) > 15 else row['U'], axis=1)

    # V列: 发电机绕组差值2不超过15
    df['V'] = df.apply(
        lambda row: min(row['V'], 15) if pd.notna(row['V']) and abs(row['F'] - row['G']) > 15 else row['V'], axis=1)

    # W列: 发电机绕组差值3不超过15
    df['W'] = df.apply(
        lambda row: min(row['W'], 15) if pd.notna(row['W']) and abs(row['G'] - row['E']) > 15 else row['W'], axis=1)

    # X列: 主轴浮动轴承温度与机舱温度差值不超过46
    df['X'] = df.apply(
        lambda row: min(row['X'], 46) if pd.notna(row['X']) and abs(row['N'] - row['J']) > 46 else row['X'], axis=1)

    return df


# 读取Excel文件
def fix_excel(file_path, output_path):
    df = pd.read_excel(file_path, engine='openpyxl')

    # 执行规则修正
    df_fixed = check_and_fix(df)

    # 保存修改后的Excel文件
    df_fixed.to_excel(output_path, index=False, engine='openpyxl')


# 示例使用
input_file = r'E:\text20241207'  # 输入文件路径
output_file = r'E:\text20241207\over'  # 输出文件路径
fix_excel(input_file, output_file)

print("处理完成，已保存至", output_file)


